Manage Database Views

Users with the Administrator role (in the ebs: intel Data Miner group) can manage custom database views.

Note: You cannot edit views supplied by Tribal.

To add or edit a database view:

1. Click the Add button on the Data Miner ribbon.

   

   Alternatively, select an existing custom database view from the Application Context panel (that is: one in bold) and then click the Edit button on the Data Miner ribbon.

   

   The Add Database View or Edit Database View window is displayed.

   

2. Enter the relevant details.

The fields on the Add Database View window are described in the following table.

This field	Holds this information...
Application Context	The application context. Refer to Use Application Contexts for further information.
View Suffix	The suffix to be appended to the Generated View Name. This can be used to overwrite an existing view by naming it the same as the view you want to overwrite.
Role	The custom role required to access the view. Refer to Add user roles for further information on creating custom roles.
Generated View Name	The database name of the view. This is generated automatically based on the Application Context and View Suffix fields.
Display Name	The name to be displayed in the Application Context panel.
SQL	The SQL code for the view. Valid code must be entered to be able to save the record. Click the Validate button to ensure that your code is valid. If the validation is successful, the grid below the button and the Time taken (secs) field are populated. Note: The SQL code should not use in-line commenting (that is: --commented code) and should use the begin/end notation (that is: /* commented code */). Notations cannot be added to the first line of SQL code. It should also not use semi-colons. This validation is also applied in Designer data links.

3. Click OK.

   The view is added and displayed in bold to denote that it is a custom view.

   

You can then use the following buttons on the custom view:

• Activate/Deactivate - Activated views are visible to all users, deactivated views are hidden from view for non-administrative users

  Note: Administrative users can use the Show Active Only check-box to control whether active or deactivated views are displayed (for example: deselect Show Active Only to display a deactivated views, which can be re-activated or deleted).

• Delete - deletes the custom view

Back to top

To access a custom view in Data Miner you must prefix the name of the view with UDM_ followed by the relevant application context prefix (for example: if you want a custom view to be displayed in the application context of Data Miner you must prefix the view name with UDM_APP_%).

You can name columns differently to determine how they are used in the grid (for example: a column that has an alias ending in '_D' (that is: p.date_of_birth AS date_of_birth_d) will be treated like a date in the grid and sort by date rather than text). Similarly you can use '_T' to format a column by time. You can also use database column comments to alter the column heading display text from the generated default. If you use an underscore character in a column name, it is removed in the column header and replaced by a space (for example: a column called 'Planned_Hours' is displayed as 'Planned Hours').

Note: Column names should not include special characters (for example: spaces, dashes or curly brackets).

You can change the column header by adding a column hint (that is: MS_Description extended property in SQL Server). This column hint can include the special characters that are mentioned above (for example: you could have a column description such as 'Adult Funded – {17/18}').

The context prefixes are described in the following table.

Name	Application Context	User View Prefix
APP	Application	UDM_APP_%
ATTEND	Attendance	UDM_REG_%
AWARD	Award	UDM_AWD_%
CAMPAIGN	Campaign	UDM_CAM_%
CPLANNING	Curriculum Planning	UDM_CPL_%
CURRICULUM	Curriculum	UDM_CUR_%
EMA	EMA	UDM_EMA_%
ENQ	Enquiry	UDM_ENQ_%
ENR	Enrolment	UDM_ENR_%
EXAM	Exam	UDM_EXA_%
ILR	ILR	UDM_ILR_%
INTER	Interview	UDM_INT_%
LLWR	LLWR	UDM_LWR_%
LEARN	Learner	UDM_LER_%
MIAP	MIAP	UDM_MIA_%
ORG	Organisation	UDM_ORG_%
PAYMENT	Payment	UDM_PAY_%
STAFF	Staff	UDM_STF_%
SUPPORT	Support	UDM_SUP_%
SYSTEM	System	UDM_SYS_%
TIMETAB	Timetable	UDM_TT_%

Note: Custom views are displayed in bold in the Application Contexts section in Data Miner.

Back to top

The Visible Columns configuration allows you to configure the default set of columns that are visible for a custom view.

Note: The All Columns configuration contains all columns in a view (that is: all user columns on a table).

To configure the default set of visible columns for a custom view, you can use the column comments to identify which columns are to be hidden. To hide a column, you need to add '¬0' to a column comment. For example: 'Person Code¬0' will result in a column header of Person Code, with the column being hidden (0 meaning false and 1 meaning true).

Note: A column without the separation character is taken to be visible. If no columns are hidden, the Visible Columns configuration is not displayed as these columns are displayed in the All Columns configuration.

The subset of columns results in an auto-generated locked default column configuration set against the Visible Columns configuration (in the grid toolbar). When further changes are made to the column configuration, this uses the normal grid behaviour to create a Custom configuration set.

Note: You should not alter the column comments of default Data Miner views.

Back to top

A locked columns configuration can be added for views in Data Miner so you can configure the set of columns that are available and name the custom configuration.

To configure a locked columns configuration for a custom view, use the column comments to identify which columns are available and enter the name of the column configuration by adding 'Configuration Name' to a column comment, after the existing visibility indicator.

Note: Anything after the special character '¬' is not shown in the column header. The first character indicates whether or not the column is shown in the Visible Columns configuration (0 meaning false and 1 meaning true). You can then optionally enter a comma and another configuration description to also show the column in this configuration.

For example:

'Surname¬1,Person List' will result in a column header of Surname, with the column being shown in both the 'Visible Columns' list and also in the 'Person List' column configuration.

'Surname¬0,Person List' will result in a column header of Surname, with the column not being shown in the 'Visible Columns' list, but available in the Person List column configuration.

Back to top

Data Miner enables you to launch a module that is directly related to the record displayed in a view (for example: if you select a course from a curriculum view, you can navigate directly to the Curriculum Details module for that course).

Note: Relevant column names must be included in a view for the particular navigation option to be displayed (for example: PERSON_CODE must be visible to enable navigation to Learner).

To launch a related module from Data Miner, right-click a record in the grid and select the Go To option, where the relevant module links are displayed.

Note: Module links are already included in standard views. You can include the functionality to launch another module from Data Miner by adding certain columns to a custom view (for example: including the PERSON_CODE column will allow you to navigate directly to the Learner Details screen).

The destination information is listed in the following table.

Column name	Database_table.Column_name	Destination screen name
PERSON_CODE	people.person_code	Learner details
STAFF_PERSON_CODE	people.person_code	Staff details
OWN_ORGANISATION_CODE OFF_ORGANISATION_CODE ORGANISATION_CODE ORGANISATION_CODE_1 ORGANISATION_CODE_2	organisation_units.organisation_code	Organisation details
FES_UNIT_INSTANCE_CODE FES_UINS_INSTANCE_CODE ILR_COURSE_CODE COURSE_CODE	unit_instances.fes_unit_instance_code	Unit Instance details
UIO_ID COURSE_ID ILR_COURSE_ID	unit_instance_occurrence.uio_id	Unit Instance occurrence details
ENROLMENT_ID	people_units.id	Enrolment details
APPLICATION_ID	people_units.id	Application details

Back to top

To add configuration data for specific columns:

1. Select Data Miner from the Modules drop-down in the Pages panel.

   

2. Click the Add Page button on the System Configuration Commands ribbon.

   

3. Set the Screen Name field in the Page Details panel to something meaningful.

   

4. Click the Add Control button.

   

5. Set the View Name field to the user view that you want to edit.

   

6. Click the Add Column button.

   

7. Set the following fields in the Column Details panel as appropriate:

   • View Columns - the column that you want to edit

   • Display Text - this is displayed in Data Miner exactly as entered

   • Visible - determines whether the column is visible in the default configuration

   • Group Description - determines whether a locked configuration of that name exists containing this column

   Note: You only have to add configuration for fields that you want to alter from the default configuration.

8. Click Save.

9. Publish the page to update the database view.

Back to top